﻿// http://stackoverflow.com/questions/4964663/why-does-this-code-cause-excel-to-not-close-properly : Never try to manage COM ObjRef yourself. Trust The GC
// http://stackoverflow.com/questions/158706/how-to-properly-clean-up-excel-interop-objects : Never use 2 dot. Better use GC.Collect & GC.WaitPending...

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Interop = Microsoft.Office.Interop;
using System.Text.RegularExpressions;

namespace DienLib.OfficeLib
{
    public class Book
    {

    }
    public class Excel : IDisposable
    {
        #region WRAPPER - Multi-Regional
        // Independant : "Book.App, Range.Count, Range.Offset, App.WorksheetFunction" is independant.
        // Dependant :   "Range.Text, Range.Value, Range.Select, WorksheetFunction.IsNumber" are Culture dependant !
        public static System.Globalization.CultureInfo Oldci;

        #region Book
        /// <summary>
        /// Open a book. Support Multi-Regional
        /// </summary>
        /// <param name="bookPath"></param>
        /// <returns></returns>
        public static Interop.Excel.Workbook BookOpen(string path, bool visible = false)
        {
            try
            {
                Oldci = System.Threading.Thread.CurrentThread.CurrentCulture;
                System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");

                Interop.Excel.Application app = new Interop.Excel.Application();
                Interop.Excel.Workbook book = app.Workbooks.Open(path); // Cross Culture Exception HERE
                if (visible) book.Application.Visible = true;
                return book;
            }
            catch (Exception e) { throw new Exception("BookOpen FAIL !", e); }
            finally { System.Threading.Thread.CurrentThread.CurrentCulture = Oldci; }
        }

        /// <summary>
        /// Copy from that workbook & create new workbook & save it. Support Multi-Regional
        /// </summary>
        /// <param name="copypath"></param>
        /// <param name="?"></param>
        /// <returns></returns>
        public static Interop.Excel.Workbook BookClone(string copypath, string newpath)
        {
            try
            {
                Oldci = System.Threading.Thread.CurrentThread.CurrentCulture;
                System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");

                Interop.Excel.Application app = new Interop.Excel.Application();
                Interop.Excel.Workbook book = app.Workbooks.Open(copypath); // Cross Culture Exception HERE
                book.SaveAs(newpath);
                return book;
            }
            catch (Exception e) { throw new Exception("BookClone FAIL !", e); }
            finally { System.Threading.Thread.CurrentThread.CurrentCulture = Oldci; }
        }

        /// <summary>
        /// Save a book. Support Multi-Regional
        /// </summary>
        /// <param name="book"></param>
        public static void BookSave(Interop.Excel.Workbook book)
        {
            try
            {
                Oldci = System.Threading.Thread.CurrentThread.CurrentCulture;
                System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");

                book.Save();
            }
            catch (Exception e) { throw new Exception("BookSave FAIL !", e); }
            finally { System.Threading.Thread.CurrentThread.CurrentCulture = Oldci; }
        }

        /*public static Book BookOpen(string path, bool visible = false)
        {
            
        }*/

        /// <summary>
        /// Close a book completely : quit app if empty then call GC. Support Multi-Regional
        /// </summary>
        /// <param name="book"></param>
        /// <param name="save"></param>
        public static void BookAppClose(Interop.Excel.Workbook book, bool save = false)
        {
            try
            {
                Oldci = System.Threading.Thread.CurrentThread.CurrentCulture;
                System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");

                Interop.Excel.Application app = book.Application;
                if (save) book.Close(SaveChanges: true); else book.Close();
                if (app.Workbooks.Count == 0) // Should not use 2 dot with COM (not much important now because use GC to clear memory)
                {
                    app.Quit();
                    //int i = System.Runtime.InteropServices.Marshal.FinalReleaseComObject(app); // Decrease RCW 1
                    //force a garbage collection when close App
                    System.GC.Collect(); // See instruction on TOP
                    System.GC.WaitForPendingFinalizers();
                }
            }
            catch (Exception e) { throw new Exception("BookAppClose FAIL !", e); }
            finally { System.Threading.Thread.CurrentThread.CurrentCulture = Oldci; }
        }

        /// <summary>
        /// Get Book Visible. Suport Multi-Regional
        /// </summary>
        /// <param name="book"></param>
        /// <returns></returns>
        public static bool BookVisible(Interop.Excel.Workbook book)
        {
            try
            {
                Oldci = System.Threading.Thread.CurrentThread.CurrentCulture;
                System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");

                return book.Application.Visible;
            }
            catch (Exception e) { throw new Exception("BookVisible Get FAIL !", e); }
            finally { System.Threading.Thread.CurrentThread.CurrentCulture = Oldci; }
        }

        /// <summary>
        /// Set Book Visible. Suport Multi-Regional
        /// </summary>
        /// <param name="book"></param>
        /// <param name="value"></param>
        public static void BookVisible(Interop.Excel.Workbook book, bool value)
        {
            try
            {
                Oldci = System.Threading.Thread.CurrentThread.CurrentCulture;
                System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");

                book.Application.Visible = value;
            }
            catch (Exception e) { throw new Exception("BookVisible Set FAIL !", e); }
            finally { System.Threading.Thread.CurrentThread.CurrentCulture = Oldci; }
        }

        #endregion

        #region Sheet
        /// <summary>
        /// Get Sheet Name. Suport Multi-Regional
        /// </summary>
        /// <param name="sheet"></param>
        /// <returns></returns>
        public static string SheetName(Interop.Excel.Worksheet sheet)
        {
            try
            {
                Oldci = System.Threading.Thread.CurrentThread.CurrentCulture;
                System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");

                return sheet.Name;
            }
            catch (Exception e) { throw new Exception("SheetName Get FAIL !", e); }
            finally { System.Threading.Thread.CurrentThread.CurrentCulture = Oldci; }
        }

        /// <summary>
        /// Set Sheet Name. Suport Multi-Regional
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="value"></param>
        public static void SheetName(Interop.Excel.Worksheet sheet, string value)
        {
            try
            {
                Oldci = System.Threading.Thread.CurrentThread.CurrentCulture;
                System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");

                sheet.Name = value;
            }
            catch (Exception e) { throw new Exception("SheetName Set FAIL !", e); }
            finally { System.Threading.Thread.CurrentThread.CurrentCulture = Oldci; }
        }


        /// <summary>
        /// Select a Sheet. Suport Multi-Regional
        /// </summary>
        /// <param name="sheet"></param>
        public static void SheetSelect(Interop.Excel.Worksheet sheet)
        {
            try
            {
                Oldci = System.Threading.Thread.CurrentThread.CurrentCulture;
                System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");

                sheet.Select();
            }
            catch (Exception e) { throw new Exception("SheetSelect FAIL !", e); }
            finally { System.Threading.Thread.CurrentThread.CurrentCulture = Oldci; }
        }
        #endregion

        #region Cell
        /// <summary>
        /// Get Cell Text. Support Multi-Regional
        /// </summary>
        /// <param name="range"></param>
        /// <returns></returns>
        public static string CellText(Interop.Excel.Range range)
        {   // Already test CellText is needed to avoid Multi Culture Problem. Only how to increase the Speed ? Benchmark later !
            try
            {
                Oldci = System.Threading.Thread.CurrentThread.CurrentCulture;
                System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");

                return range.Text as string;
            }
            catch (Exception e) { throw new Exception("CellText FAIL !", e); }
            finally { System.Threading.Thread.CurrentThread.CurrentCulture = Oldci; }
        }

        /// <summary>
        /// Get Cell Value. Support Multi-Regional
        /// </summary>
        /// <param name="range"></param>
        /// <returns></returns>
        public static object CellValue(Interop.Excel.Range range)
        {
            try
            {
                Oldci = System.Threading.Thread.CurrentThread.CurrentCulture;
                System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");

                return range.Value;
            }
            catch (Exception e) { throw new Exception("CellValue Get FAIL !", e); }
            finally { System.Threading.Thread.CurrentThread.CurrentCulture = Oldci; }
        }

        /// <summary>
        /// Set Cell Value. Support Multi-Regional
        /// </summary>
        /// <param name="range"></param>
        /// <param name="value"></param>
        public static void CellValue(Interop.Excel.Range range, object value)
        {
            try
            {
                Oldci = System.Threading.Thread.CurrentThread.CurrentCulture;
                System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");

                range.Value = value;
            }
            catch (Exception e) { throw new Exception("CellValue Set FAIL !", e); }
            finally { System.Threading.Thread.CurrentThread.CurrentCulture = Oldci; }
        }

        /// <summary>
        /// Get Cell Address. Support Multi-Regional
        /// </summary>
        /// <param name="range"></param>
        /// <returns></returns>
        public static string CellAddress(Interop.Excel.Range range)
        {
            try
            {
                Oldci = System.Threading.Thread.CurrentThread.CurrentCulture;
                System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");

                return range.Address;
            }
            catch (Exception e) { throw new Exception("CellAddress FAIL !", e); }
            finally { System.Threading.Thread.CurrentThread.CurrentCulture = Oldci; }
        }

        /// <summary>
        /// Select a cell. Suport Multi-Regional
        /// </summary>
        /// <param name="range"></param>
        public static void CellSelect(Interop.Excel.Range range)
        {
            try
            {
                Oldci = System.Threading.Thread.CurrentThread.CurrentCulture;
                System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");

                range.Select();
            }
            catch (Exception e) { throw new Exception("CellSelect FAIL !", e); }
            finally { System.Threading.Thread.CurrentThread.CurrentCulture = Oldci; }
        }
        #endregion

        #region Other
        /// <summary>
        /// Function IsNumber. Suport Multi-Regional
        /// </summary>
        /// <param name="function"></param>
        /// <param name="range"></param>
        /// <returns></returns>
        public static bool FunctionIsNumber(Interop.Excel.WorksheetFunction function, Interop.Excel.Range range)
        {
            try
            {
                Oldci = System.Threading.Thread.CurrentThread.CurrentCulture;
                System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");

                return function.IsNumber(range);
            }
            catch (Exception e) { throw new Exception("FunctionIsNumber FAIL !", e); }
            finally { System.Threading.Thread.CurrentThread.CurrentCulture = Oldci; }
        }
        #endregion

        #endregion

        //======================================================================================================================//
        //======================================================================================================================//
        //=======================================================STATIC PART====================================================//
        //======================================================================================================================//
        //======================================================================================================================//

        public static Interop.Excel.Range ReadRange(string filePath, string sheetname, string address)
        {
            Interop.Excel.Workbook book = BookOpen(filePath);
            Interop.Excel.Worksheet sheet = getSheet(book, sheetname);
            return sheet.Range[address];
        }

        public static string ReadCell(string fileName, string sheetname, string address)
        {
            return ReadRange(fileName, sheetname, address).Text as string;
        }

        public static string ReadCell(Interop.Excel.Worksheet sheet, string address)
        {
            return sheet.Range[address].Text as string;
        }

        public static void WriteCell(Interop.Excel.Worksheet sheet, string address, string value)
        {
            sheet.Range[address].Value = value;
        }

        public static void OpenBookVisible(string bookPath)
        {
            BookOpen(bookPath).Application.Visible = true;
        }

        public static void CloneBookVisible(string copypath, string newpath)
        {
            BookClone(copypath, newpath).Application.Visible = true;
        }

        private static Interop.Excel.Worksheet getSheet(Interop.Excel.Workbook book, string sheetName)
        {
            foreach (Interop.Excel.Worksheet sheet in book.Worksheets)
            {
                string txt = sheet.Name.ToLower();
                if (txt == sheetName.ToLower()) return sheet;
            }

            return null;
        }

        //=============================================================================//
        //=============================================================================//
        //============================= NONE STATIC PART ==============================//
        //=============================================================================//
        //=============================================================================//
        private Interop.Excel.Workbook g_book = null;
        private Interop.Excel.Worksheet g_sheet = null;
        private string g_bookPath = string.Empty;

        public Excel()
        {

        }

        ~Excel()   // With C++ Destructor will always be called. But in C# 'Cause Destructor can be called right quick, can be 1 hour later or even never be called.
        {           // please use IDisposal Pattern above instead Finalize or Destructor
            // Simply call Dispose(false).
            Dispose(false);
        }

        #region IDisposable Members
        public void Dispose() // Explicit (user call outside) call to this method (khac voi implicit call - Gabage Collector tu dong goi)
        {
            Dispose(true); // Goi ham Dispose chinh
            GC.SuppressFinalize(this); // Tranh goi Finalizer (dich noi tu Destructor <- Call Implicit form Gabarge Collector)
        }

        protected virtual void Dispose(bool disposing) // Ham dispose chinh override IDisposable (se tu dong duoc goi neu cu phap "using"
        {
            if (disposing) // Neu dispose(true) tuc la Explicit call nghia la co thang keu ham Dispose truc tiep
            {
                // Free other state (manage objects).
            }
            // Free your own state (unmanage objects).

            // Set large field to null.
            // FinalRemoveComObj ...v..v..
            if (g_book != null)
            {
                try { BookAppClose(g_book); }
                catch (Exception e) { }
                System.Runtime.InteropServices.Marshal.FinalReleaseComObject(g_sheet); // NEED TEST
                System.Runtime.InteropServices.Marshal.FinalReleaseComObject(g_book);
                g_book = null; g_sheet = null; g_bookPath = null;
            }
            GC.Collect(); // GC will collect this : call Destructor 1 lan nua ??? Tao loop vo tan a ? KHONG
            GC.WaitForPendingFinalizers(); // Wait here for long ? Need Test
        }
        #endregion


        public bool IsBlankCell(int row, int column)
        {
            if ((CellText(g_sheet.Cells[row, column] as Interop.Excel.Range)).Trim() == "") return true;
            else return false;
        }

        public static bool IsBookPath(string bookPath)
        {
            if (bookPath == "") return false;
            if (bookPath.EndsWith(".xls") | bookPath.EndsWith(".xlsx") | bookPath.EndsWith(".xlsm")) return true;
            else return false;
        }

        /// <summary>
        /// Use InitBook & Close Book instead
        /// </summary>
        public string BookPath
        {
            get { return g_bookPath; }
            set
            {
                g_bookPath = value;
                if (g_book != null) g_book.Close(); // Close first if still Open
                g_book = BookOpen(g_bookPath);
            }
        }

        /// <summary>
        /// Init Book before ReadCell to save ReOpen each time call ReadCell. All ready implement case reopen a same file
        /// If Book already Open. Then will Init Sheet only !
        /// </summary>
        /// <param name="bookPath">Book Path to Open</param>
        /// <param name="sheetName">Sheet Name to Activate. Can call ActivateSheet instead</param>
        /// <param name="readCellSilent">default false. true Will read this Book Silently even when found error</param>
        /// <param name="visible">default false Will read this Book Invisiblely</param>
        public void InitBook(string bookPath, string sheetName, bool readCellSilent = false, bool visible = false)
        {
            // Neu ma duong dan giong thi kiem tra neu null thi tao moi, neu khong null thi tro ve
            // Neu ma duong dan khac thi kiem tra neu null thi tao moi, neu khong null thi close va tao moi
            if (!IsBookPath(bookPath)) throw new InvalidOperationException("Not an Excel Workbook Path");

            if (g_bookPath == bookPath & g_book != null) // Neu mo lai cung tap tin thi thoi khoi.
            {
                g_sheet = getSheet(g_book, sheetName);
                if (g_sheet == null) g_sheet = g_book.Worksheets[1] as Interop.Excel.Worksheet; // 1 index for Worksheet
                _ReadCellSilent = readCellSilent;
            }
            else if (g_bookPath == bookPath & g_book == null) // Neu cung pathName nhung g_book chua tao thi Tao moi g_book
            {
                g_book = BookOpen(g_bookPath, visible);

                g_sheet = getSheet(g_book, sheetName);
                if (g_sheet == null) g_sheet = g_book.Worksheets[1] as Interop.Excel.Worksheet; // 1 index for Worksheet
                _ReadCellSilent = readCellSilent;
            }
            else if (g_bookPath != bookPath & g_book != null) // Neu khac duong dan & tap tin thi dong lai truoc
            {   // Else Tap tin khac hoac g_book null
                //g_book.Close(); // Close first if still Open
                BookAppClose(g_book); // Quit App luon, tao App moi cho no sang
                g_book = null;
                //System.Threading.Thread.Sleep(2000); // Sleep for a while

                g_bookPath = bookPath;
                g_book = BookOpen(g_bookPath, visible);
                g_sheet = getSheet(g_book, sheetName);
                if (g_sheet == null) g_sheet = g_book.Worksheets[1] as Interop.Excel.Worksheet; // 1 index for Worksheet
                _ReadCellSilent = readCellSilent;
            }
            else if (g_bookPath != bookPath & g_book == null) // Neu khac duong dan & chua mo tap tin
            {
                g_bookPath = bookPath;
                g_book = BookOpen(g_bookPath, visible);
                g_sheet = getSheet(g_book, sheetName);
                if (g_sheet == null) g_sheet = g_book.Worksheets[1] as Interop.Excel.Worksheet; // 1 index for Worksheet
                _ReadCellSilent = readCellSilent;
            }
        }

        public void ActivateSheet(string sheetName)
        {
            if (g_book == null) throw new InvalidOperationException("g_book is null");
            g_sheet = getSheet(g_book, sheetName);
            if (g_sheet == null) throw new InvalidOperationException("No sheet name :" + sheetName + " in book : " + g_book.Name);
        }

        /// <summary>
        /// Init Book before ReadCell to save ReOpen each time call ReadCell
        /// </summary>
        /// <param name="bookPath"></param>
        public void InitBook(Interop.Excel.Workbook book, string sheetName, bool readCellSilent = false)
        {
            if (book == null) throw new InvalidOperationException("Book Null, cannot do anything !");
            // Book != null :
            if (g_book != null) // g_book != null thi xem co phai cung 1 book khong, neu cung thi mo sheet, neu khac dong g_book lai truoc cai da
            {
                if (g_book.Equals(book))
                {
                    g_sheet = getSheet(g_book, sheetName);
                    if (g_sheet == null) g_sheet = g_book.Worksheets[1] as Interop.Excel.Worksheet;
                    _ReadCellSilent = readCellSilent;
                }
                else
                {
                    //g_book.Close(); // Close first if still Open
                    BookAppClose(g_book); // Quit App luon, tao App moi cho no sang
                    g_book = null;
                    System.Threading.Thread.Sleep(2000); // Sleep for a while

                    g_bookPath = book.Path + @"\" + book.Name;
                    g_book = book;
                    g_sheet = getSheet(g_book, sheetName);
                    if (g_sheet == null) g_sheet = g_book.Worksheets[1] as Interop.Excel.Worksheet;
                    _ReadCellSilent = readCellSilent;
                }
            }
            else
            { // g_book == null thi khoi tao g_book moi & bookPath moi
                g_bookPath = book.Path + @"\" + book.Name;
                g_book = book;
                g_sheet = getSheet(g_book, sheetName);
                if (g_sheet == null) g_sheet = g_book.Worksheets[1] as Interop.Excel.Worksheet;
                _ReadCellSilent = readCellSilent;
            }
        }

        public bool _ReadCellSilent;
        public bool ReadCellSilent
        {
            get { return _ReadCellSilent; }
            set { _ReadCellSilent = value; }
        }

        private void HighlightCell(Interop.Excel.Range cell)
        {
            try
            {
                if (SheetName(cell.Worksheet) != SheetName(cell.Application.ActiveSheet as Interop.Excel.Worksheet)) SheetSelect(cell.Worksheet);
                CellSelect(cell); System.Threading.Thread.Sleep(100);
                //With Activate, if you presently have more than one sheet selected, the ActiveSheet will change, but all selected sheets will remain selected.
                //Select can select multiple objects. Activate can activate only one object. To select a cell or a range of cells, use the Select method. To make a single cell the active cell, use the Activate method.
            }
            catch (Exception e) { if (!e.Message.Contains("select")) throw e; }
        }

        public void WriteCell<T>(int row, int column, T value)
        {
            Interop.Excel.Range range = g_sheet.Cells[row, column] as Interop.Excel.Range;
            try
            {
                Oldci = System.Threading.Thread.CurrentThread.CurrentCulture;
                System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");

                if (typeof(T) == typeof(DateTime)) range.Value = value; //(T)(object)Convert.ToDateTime(range.Text);
                else if (typeof(T) == typeof(double)) range.Value = value;
                else if (typeof(T) == typeof(int)) range.Value = value;
                else if (typeof(T) == typeof(string)) range.Value = value;
                else throw new InvalidOperationException("Not Support Value Type T in WriteCell Function. Only DateTime, Double & Int & String are supported"); // Not Supported Type, throw Ex
            }
            catch (Exception e)
            {
                DienLib.PopUp.PopUpOK.ShowDialog("Can not Write Cell, something wrong !\r\n" + e.Message, "WRITE CELL FAIL!");
            }
            finally { System.Threading.Thread.CurrentThread.CurrentCulture = Oldci; }
        }


        public T ReadCell<T>(int row, int column)
        {   // Tested MultiReligion already. Sheet.Range[] is safe for Multi_Religion
            Interop.Excel.Range range = g_sheet.Cells[row, column] as Interop.Excel.Range;
            return ReadCell<T>(range);
        }

        public T ReadCell<T>(string address)
        {   // Tested MultiReligion already. Sheet.Range[] is safe for Multi_Religion
            Interop.Excel.Range range = g_sheet.Range[address];
            return ReadCell<T>(range);
        }

        public T ReadCell<T>(Interop.Excel.Range range)
        {   // Tested MultiReligion already
        __RETRY_GETVALUE__:
            string txt = CellText(range);
            if (txt.Trim() == "" | Regex.IsMatch(txt, @"n[/\\]a", RegexOptions.IgnoreCase))
            {   // Neu cell Empty hoac N/A thi tra ve gia tri 0 hoac DateTime.No
                if (!_ReadCellSilent)
                {
                    BookVisible(g_book, true);
                    // Bring it On Top also
                    HighlightCell(range);
                    PopUp.PopUpResult _pResult = PopUp.PopUpRetry.ShowDialog(out _ReadCellSilent, string.Format("Cell \"{0}\" contains \"{1}\" is BLANK or NOT APPLICABLE.\r\n" +
                        "Will auto put ZERO or DateTime.NOW if you click NEXT!\r\n" +
                        "\r\nYOU CAN \"FIX IT & TRY AGAIN ?\"", CellAddress(range).Replace("$", ""), txt), "YOU FORGET FILL VALUE !");
                    if (_pResult == PopUp.PopUpResult.RETRY) goto __RETRY_GETVALUE__; // RETRY DIALOG BOX
                }
                // to FINAL
            }
            else
            {   // Neu cell khong Empty hoac N/A thi Parse
                try
                {
                    if (typeof(T) == typeof(DateTime)) return (T)CellValue(range); // (T)(object)Convert.ToDateTime(range.Text);
                    else if (typeof(T) == typeof(double)) return (T)CellValue(range);
                    else if (typeof(T) == typeof(int)) return (T)(object)Convert.ToInt32(CellValue(range)); // Need converter because value return is double
                    else if (typeof(T) == typeof(string)) return (T)(object)txt; // HERE IF column too small will cause number (even as text) become "#####"
                    else throw new InvalidOperationException("Not Support Value Type T in getValue Function. Only DateTime, Double & Int are supported"); // Not Supported Type, throw Ex
                }
                catch (Exception e)
                {
                    if (!_ReadCellSilent)
                    {
                        BookVisible(g_book, true);
                        // Bring it On Top also
                        HighlightCell(range);
                        if (!e.Message.Contains("cast")) throw e;
                        PopUp.PopUpResult _pResult = PopUp.PopUpRetry.ShowDialog(out _ReadCellSilent, string.Format("Cannot get Value at Cell \"{0}\" contains \"{1}\"\r\n" +
                            "May be you put not correct Syntax Format\r\n" +
                            "\r\nYOU CAN \"FIX IT & TRY AGAIN ?\"", CellAddress(range).Replace("$", ""), txt), "YOU FILL WRONG SYNTAX!");
                        if (_pResult == PopUp.PopUpResult.RETRY) goto __RETRY_GETVALUE__; // RETRY DIALOG BOX
                    }
                    // to FINAL
                }
            }
            // FINAL : mean BLANK | N/A | Parse Correct Type but Fail : throw Exception, MessageBox already
            if (typeof(T) == typeof(DateTime)) return (T)(object)DateTime.Now;
            else if (typeof(T) == typeof(double)) return (T)(object)0.0;
            else if (typeof(T) == typeof(int)) return (T)(object)0;
            else if (typeof(T) == typeof(string)) return (T)(object)"";
            else throw new InvalidOperationException("Not Support Value Type T in getValue Function. Only DateTime, Double & Int are supported");
        }

        /// <summary>
        /// LookDown on Column until see Match or see Until Condition. If nothing see, limited to 1000. Use with InitBook & CloseBook
        /// </summary>
        /// <param name="regexMatch"></param>
        /// <param name="regexUntil">Limited to 1000 Row Down</param>
        /// <param name="curRow"></param>
        /// <param name="curCol"></param>
        /// <returns></returns>
        public int LookDown(string regexMatch, string regexUntil, int curRow, int curCol)
        {
            if (g_sheet == null) throw new InvalidOperationException("Please Init Book first");
            for (int iRow = curRow; iRow < curRow + 1000; iRow++)
            {
                string s = (g_sheet.Cells[iRow, curCol] as Interop.Excel.Range).Text as string;
                if (Regex.IsMatch(s, regexMatch, RegexOptions.IgnoreCase)) // If see Match tra ve iRow
                    return iRow;
                else if (Regex.IsMatch(s, regexUntil, RegexOptions.IgnoreCase)) // Khong thi If see Until tra ve -1
                    return -1;
            }
            return -1;
        }

        public int LookRight(string regexMatch, string regexUntil, int curRow, int curCol)
        {
            if (g_sheet == null) throw new InvalidOperationException("Please Init Book first");
            for (int iCol = curCol; iCol < curCol + 50; iCol++)
            {
                string s = (g_sheet.Cells[curRow, iCol] as Interop.Excel.Range).Text as string;
                if (Regex.IsMatch(s, regexMatch, RegexOptions.IgnoreCase)) // If see Match tra ve iRow
                    return iCol;
                else if (Regex.IsMatch(s, regexUntil, RegexOptions.IgnoreCase)) // Khong thi If see Until tra ve -1
                    return -1;
            }
            return -1;
        }








        /// <summary>
        /// Save Book
        /// </summary>
        public void SaveBook()
        {
            BookSave(g_book);
        }

        /// <summary>
        /// Close Book after Read All Cell Finish
        /// </summary>
        public void CloseBook(bool save = false)
        {
            BookAppClose(g_book, save);
            System.Runtime.InteropServices.Marshal.FinalReleaseComObject(g_sheet);
            System.Runtime.InteropServices.Marshal.FinalReleaseComObject(g_book);
            g_book = null; g_sheet = null; g_bookPath = string.Empty;
            GC.Collect(); GC.WaitForPendingFinalizers();
        }
    }
}

